package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.queryParams.HisPrQueryParams;
import com.css.fxfzypg.modules.baseData.repository.HisPrRepository;
import com.css.fxfzypg.modules.baseData.entity.HisPrEntity;
import com.css.fxfzypg.modules.baseData.vo.HisPrExcelVo;
import com.css.fxfzypg.modules.similitudeAnalyse.entity.RealEarthquakeEntity;
import com.css.fxfzypg.modules.similitudeAnalyse.entity.SimAnaResult;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author y1fan
 * @create 2022-08-22 10:52
 */
@Repository
public class HisPrRepositoryImpl implements HisPrRepository {


    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    /*
    dhp_task_name,dhp_earthquake_number,dhp_epicenter_longitude," +
                "dhp_epicenter_latitude,dhp_location,dhp_magnitude,dhp_di_area,dhp_di_people,dhp_di_gdp," +
                "dhp_di_ec_losses,dhp_es_death,dhp_es_injured,dhp_es_res_number,dhp_need_eme_assi,dhp_pro_res_force,dhp_civ_rel_efforts,dhp_other_forces,dhp_es_tents_num," +
                "dhp_es_stretcher_num,dhp_es_quilt_num,dhp_es_food_num,dhp_es_water_num," +
                "dhp_es_toilet_num,dhp_es_bathroom_num,dhp_es_staff_num," +
                "dhp_es_doctor_num,dhp_es_nurse_num,dhp_es_psy_doctor_num," +
                "dhp_es_ward_num,dhp_es_bed_num,dhp_es_plasma_num," +
                "dhp_di_analysis,dhp_strategies,dhp_pre_measures,dhp_res_measures,dhp_short_form,dhp_detailed,dhp_createtime,dhp_isdelete,dhp_create_user
     */


    @Override
    public boolean saveHisPr(HisPrEntity hisPrEntity) {
        String sql = "insert into data_his_pr values(:id,:dhpYear,:dhpTaskNum,:dhpTaskName,:dhpEarthquakeNum,:dhpEpicenterLongitude,:dhpEpicenterLatitude,:dhpLocation," +
                ":dhpMagnitude,:dhpDiArea,:dhpDiPeople,:dhpDiGdp,:dhpEsDeath,:dhpEsInjured,:dhpDiEcLosses,:dhpEsResNumber,:dhpNeedEmeAssi,:dhpProResForce,:dhpCivRelEfforts,:dhpOtherForces," +
                ":dhpEsTentsNum,:dhpEsStretcherNum,:dhpEsQuiltNum,:dhpEsFoodNum,:dhpEsWaterNum,:dhpEsToiletNum,:dhpEsBathroomNum,:dhpEsStaffNum,:dhpEsDoctorNum,:dhpEsNurseNum,:dhpEsPsyDoctorNum," +
                ":dhpEsWardNum,:dhpEsBedNum,:dhpEsPlasmaNum,:provinceId,:delFlag,:dhpCreateUser,:dhpCreateTime,:dhpUpdateUser,:dhpUpdateTime) ";

        SqlParameterSource source = new BeanPropertySqlParameterSource(hisPrEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public HisPrEntity getHisPrById(String id) {
        String sql = "select * from data_his_pr where id ='" + id + "' and del_flag ='" + YNEnum.N.toString() + "'";
        HisPrEntity hisPrEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(HisPrEntity.class));
        return hisPrEntity;
    }

    @Override
    public boolean updateHisPr(HisPrEntity hisPrEntity) {
        String sql = "update data_his_pr set dhp_year = :dhpYear, dhp_task_num = :dhpTaskNum, dhp_task_name = :dhpTaskName, dhp_earthquake_num = :dhpEarthquakeNum, dhp_epicenter_longitude = :dhpEpicenterLongitude, dhp_epicenter_latitude = :dhpEpicenterLatitude, dhp_location = :dhpLocation, dhp_magnitude = :dhpMagnitude," +
                " dhp_di_area = :dhpDiArea, dhp_di_people = :dhpDiPeople, dhp_di_gdp = :dhpDiGdp, dhp_di_ec_losses = :dhpDiEcLosses, dhp_es_death = :dhpEsDeath, dhp_es_injured = :dhpEsInjured, dhp_es_res_number = :dhpEsResNumber, dhp_need_eme_assi = :dhpNeedEmeAssi, dhp_pro_res_force = :dhpProResForce, " +
                "dhp_civ_rel_efforts = :dhpCivRelEfforts, dhp_other_forces = :dhpOtherForces ,dhp_es_tents_num=:dhpEsTentsNum,dhp_es_stretcher_num =:dhpEsStretcherNum,dhp_es_quilt_num =:dhpEsQuiltNum,dhp_es_food_num =:dhpEsFoodNum,dhp_es_water_num =:dhpEsWaterNum ,dhp_es_toilet_num=:dhpEsToiletNum," +
                "dhp_es_bathroom_num=:dhpEsBathroomNum,dhp_es_staff_num=:dhpEsStaffNum,dhp_es_doctor_num=:dhpEsDoctorNum,dhp_es_nurse_num=:dhpEsNurseNum,dhp_es_psy_doctor_num=:dhpEsPsyDoctorNum,dhp_es_ward_num=:dhpEsWardNum,dhp_es_bed_num=:dhpEsBedNum,dhp_es_plasma_num=:dhpEsPlasmaNum," +
                "del_flag=:delFlag,dhp_update_user=:dhpUpdateUser,dhp_update_time=:dhpUpdateTime where id = '" + hisPrEntity.getId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(hisPrEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public Map<String, Object> getAllHisPrByParams(HisPrQueryParams queryParams, String provinceId) {
        StringBuilder sql = new StringBuilder(" select * from data_his_pr ");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '" + YNEnum.N.toString() + "' and province_id = '" + provinceId + "'");
        StringBuilder orderSql = new StringBuilder(" order by dhp_year,dhp_earthquake_num,dhp_magnitude ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getDhpTaskName())) {
            whereSql.append(" and dhp_task_name like '%" + queryParams.getDhpTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getDhpMagnitude())) {
            whereSql.append(" and cast(dhp_magnitude as VARCHAR) like '%" + queryParams.getDhpMagnitude() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getDhpYear())) {
            whereSql.append(" and dhp_year = '" + queryParams.getDhpYear() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getDhpLocation())) {
            whereSql.append(" and dhp_location like '%" + queryParams.getDhpLocation() + "%'");
        }
        int firstIndex = (queryParams.getCurPage() - 1) * queryParams.getPageSize();
        int lastIndex = queryParams.getPageSize();
        sql.append(whereSql).append(orderSql);
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(HisPrEntity.class)).size();
        List<HisPrEntity> hisPrEntities = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(HisPrEntity.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", hisPrEntities);
        return map;
    }

    @Override
    public Map<String, Object> getAllHisPrByParams(HisPrQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select * from data_his_pr ");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '" + YNEnum.N.toString() + "' ");
        StringBuilder orderSql = new StringBuilder(" order by dhp_year desc , dhp_task_name,dhp_earthquake_num,dhp_magnitude ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getDhpTaskName())) {
            whereSql.append(" and dhp_task_name like '%" + queryParams.getDhpTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getDhpMagnitude())) {
            whereSql.append(" and cast(dhp_magnitude as VARCHAR) like '%" + queryParams.getDhpMagnitude() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getDhpYear())) {
            whereSql.append(" and dhp_year = '" + queryParams.getDhpYear() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getDhpLocation())) {
            whereSql.append(" and dhp_location like '%" + queryParams.getDhpLocation() + "%'");
        }
        int firstIndex = (queryParams.getCurPage() - 1) * queryParams.getPageSize();
        int lastIndex = queryParams.getPageSize();
        sql.append(whereSql).append(orderSql);
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(HisPrEntity.class)).size();
        List<HisPrEntity> hisPrEntities = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(HisPrEntity.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", hisPrEntities);
        return map;
    }

    @Override
    public boolean deleteHisPr(List<String> ids, Timestamp currentTimestamp, String userID) {
        String sql = "update data_his_pr set del_flag = '" + YNEnum.Y.toString() + "' ,dhp_update_time ='" + currentTimestamp + "' ,dhp_update_user='" + userID + "' where id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        return namedParameterJdbcTemplate.update(sql, map) > 0;
    }

    @Override
    public List<HisPrEntity> exportExcel() {
        String sql = "select * from data_his_pr where del_flag='" + YNEnum.N.toString() + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HisPrEntity.class));
    }

    /*@Override
    public void importExcel(List<HisPrEntity> hisPrEntities) {
        jdbcTemplate.batchUpdate("insert into data_his_pr(dhp_id,dhp_year,dhp_task_name,dhp_earthquake_number,dhp_epicenter_longitude," +
                "dhp_epicenter_latitude,dhp_location,dhp_magnitude,dhp_di_area,dhp_di_people,dhp_di_gdp," +
                "dhp_di_ec_losses,dhp_es_death,dhp_es_injured,dhp_es_res_number,dhp_need_eme_assi,dhp_pro_res_force,dhp_civ_rel_efforts," +
                "dhp_other_forces,dhp_es_tents_num," +
                "dhp_es_stretcher_num,dhp_es_quilt_num,dhp_es_food_num,dhp_es_water_num," +
                "dhp_es_toilet_num,dhp_es_bathroom_num,dhp_es_staff_num," +
                "dhp_es_doctor_num,dhp_es_nurse_num,dhp_es_psy_doctor_num," +
                "dhp_es_ward_num,dhp_es_bed_num,dhp_es_plasma_num," +
                "dhp_di_analysis,dhp_strategies,dhp_pre_measures,dhp_res_measures," +
                "dhp_short_form,dhp_detailed,dhp_createtime,dhp_isdelete) " +
                "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1,hisPrEntities.get(i).getId());
                ps.setString(2,hisPrEntities.get(i).getDhpYear());
                ps.setString(3,hisPrEntities.get(i).getDhpTaskName());
                ps.setString(4,hisPrEntities.get(i).getDhpEarthquakeNum());
                ps.setFloat(5,hisPrEntities.get(i).getDhpEpicenterLongitude());
                ps.setFloat(6,hisPrEntities.get(i).getDhpEpicenterLatitude());
                ps.setString(7,hisPrEntities.get(i).getDhpLocation());
                ps.setFloat(8,hisPrEntities.get(i).getDhpMagnitude());
                ps.setFloat(9,hisPrEntities.get(i).getDhpDiArea());
                ps.setFloat(10,hisPrEntities.get(i).getDhpDiPeople());
                ps.setFloat(11,hisPrEntities.get(i).getDhpDiGdp());
                ps.setString(12,hisPrEntities.get(i).getDhpDiEcLosses());
                ps.setString(13,hisPrEntities.get(i).getDhpEsDeath());
                ps.setString(14,hisPrEntities.get(i).getDhpEsInjured());
                ps.setString(15,hisPrEntities.get(i).getDhpEsResNumber());
                ps.setString(16,hisPrEntities.get(i).getDhpNeedEmeAssi());
                ps.setString(17,hisPrEntities.get(i).getDhpProResForce());
                ps.setString(18,hisPrEntities.get(i).getDhpCivRelEfforts());
                ps.setString(19,hisPrEntities.get(i).getDhpOtherForces());
                ps.setString(20,hisPrEntities.get(i).getDhpEsTentsNum());
                ps.setString(21,hisPrEntities.get(i).getDhpEsStretcherNum());
                ps.setString(22,hisPrEntities.get(i).getDhpEsQuiltNum());
                ps.setString(23,hisPrEntities.get(i).getDhpEsFoodNum());
                ps.setString(24,hisPrEntities.get(i).getDhpEsWaterNum());
                ps.setString(25,hisPrEntities.get(i).getDhpEsToiletNum());
                ps.setString(26,hisPrEntities.get(i).getDhpEsBathroomNum());
                ps.setString(27,hisPrEntities.get(i).getDhpEsStaffNum());
                ps.setString(28,hisPrEntities.get(i).getDhpEsDoctorNum());
                ps.setString(29,hisPrEntities.get(i).getDhpEsNurseNum());
                ps.setString(30,hisPrEntities.get(i).getDhpEsPsyDoctorNum());
                ps.setString(31,hisPrEntities.get(i).getDhpEsWardNum());
                ps.setString(32,hisPrEntities.get(i).getDhpEsBedNum());
                ps.setString(33,hisPrEntities.get(i).getDhpEsPlasmaNum());
                ps.setString(34,hisPrEntities.get(i).getDhpDiAnalysis());
                ps.setString(35,hisPrEntities.get(i).getDhpStrategies());
                ps.setString(36,hisPrEntities.get(i).getDhpPreMeasures());
                ps.setString(37,hisPrEntities.get(i).getDhpResMeasures());
                ps.setString(38,hisPrEntities.get(i).getDhpShortForm());
                ps.setString(39,hisPrEntities.get(i).getDhpDetailed());
                java.sql.Date createDate = new java.sql.Date(hisPrEntities.get(i).getDhpCreateTime().getTime());
                ps.setDate(38,  createDate);
                ps.setString(39,hisPrEntities.get(i).getDelFlag());
            }
            @Override
            public int getBatchSize() {
                return hisPrEntities.size();
            }
        });
    }*/

    @Override
    public List<HisPrEntity> selectSimilarity(RealEarthquakeEntity realEarthquake) {
        StringBuilder sql = new StringBuilder(" select  u.* from data_his_pr u ");

        StringBuilder whereSql = new StringBuilder(" where  -0.3 < '" + realEarthquake.getM() + "' - u.dhp_magnitude  and '" + realEarthquake.getM() + "' - u.dhp_magnitude < 0.3  and  dhp_isdelete ='" + YNEnum.N.toString() + "'");

     /*   if (!PlatformObjectUtils.isEmpty(queryParams.getDhpLocation())) {
            whereSql.append(" and u.dhp_location like %" + queryParams.getDhpLocation() + "%");
        }*/

    /*    if (!PlatformObjectUtils.isEmpty(queryParams.getDhLatitude())) {
            whereSql.append(" and u.dh_latitude like %" + queryParams.getDhLatitude() + "%");
        }
*/
        String wholeSql = sql.append(whereSql).toString();
        List<HisPrEntity> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(HisPrEntity.class));
        return list;
    }

    @Override
    public HisPrExcelVo getHisPrVoById(String id) {
        String sql = "select * from data_his_pr where id ='" + id + "' and del_flag ='" + YNEnum.N.toString() + "'";
        HisPrExcelVo hisPrExcelVo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(HisPrExcelVo.class));
        return hisPrExcelVo;
    }

    @Override
    public List<HisPrExcelVo> getNoPageHisPrByParams(HisPrQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select * from data_his_pr ");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '" + YNEnum.N.toString() + "' ");
        StringBuilder orderSql = new StringBuilder(" order by dhp_year,dhp_earthquake_num,dhp_magnitude ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getDhpTaskName())) {
            whereSql.append(" and dhp_task_name like '%" + queryParams.getDhpTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getDhpMagnitude())) {
            whereSql.append(" and cast(dhp_magnitude as VARCHAR) like '%" + queryParams.getDhpMagnitude() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getDhpYear())) {
            whereSql.append(" and dhp_year = '" + queryParams.getDhpYear() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getDhpLocation())) {
            whereSql.append(" and dhp_location like '%" + queryParams.getDhpLocation() + "%'");
        }
        sql.append(whereSql).append(orderSql);
        List<HisPrExcelVo> hisPrEntities = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(HisPrExcelVo.class));
        return hisPrEntities;
    }


    @Override
    public String getProvinceName(String param) {
        String sql = "SELECT t.name provinceName FROM district_boundary_province t WHERE ST_Within(ST_GeometryFromText('POINT (" + param + ")',4490),t.geom)";
        String provinceName = null;
        try {
            provinceName = jdbcTemplate.queryForObject(sql, String.class);
        } catch (DataAccessException e) {
            return null;
        }
        return provinceName;
    }

    @Override
    public List<HisPrEntity> getDataByLongitudeAndLatitude(String epiLon, String epiLat, BigDecimal o, String m) {
        String sql = "select * from data_his_pr where st_intersects((select a.geom from district_boundary_county a " +
                "where st_intersects(st_geomfromtext('POINT(" + epiLon + " " + epiLat + ")',4490),a.geom)), " +
                "(select st_geometryfromtext('POINT(' || dhp_epicenter_longitude || ' ' || dhp_epicenter_latitude || ')',4490))) " +
                "or st_intersects((select st_transform(st_buffer(st_transform(st_geomfromtext('POINT(" + epiLon + " " + epiLat + ")',4490), 4527)," + o + "),4490)), " +
                "(select st_geometryfromtext('POINT(' || dhp_epicenter_longitude || ' ' || dhp_epicenter_latitude || ')',4490))) " +
                "group by id having (dhp_magnitude-" + m + ")<=0.3 and ( dhp_magnitude - " + m + " ) >= -0.3";

        List<HisPrEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HisPrEntity.class));
        return list;
    }

    @Override
    public HisPrEntity getDateById(String hisPrId) {
        String sql = "select * from data_his_pr where id = ? ";
        HisPrEntity hisPrEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(HisPrEntity.class), hisPrId);
        return hisPrEntity;
    }

    @Override
    public Integer countByCounty(String name) {
        return jdbcTemplate.queryForObject("select count(a.*) from data_his_pr a,district_boundary_county t where st_intersects(st_geomfromtext('POINT('||a.dhp_epicenter_longitude||' '||a.dhp_epicenter_latitude||')',4490), t.geom) and t.name='" + name + "' and a.del_flag ='0' ", Integer.class);
    }

    @Override
    public Integer countByCity(String name) {
        return jdbcTemplate.queryForObject("select count(a.*) from data_his_pr a,district_boundary_city t where st_intersects(st_geomfromtext('POINT('||a.dhp_epicenter_longitude||' '||a.dhp_epicenter_latitude||')',4490), t.geom) and t.name='" + name + "' and a.del_flag ='0'  ", Integer.class);
    }

    @Override
    public Integer countByProvince(String name) {
        return jdbcTemplate.queryForObject("select count(a.*) from data_his_pr a,district_boundary_province t where st_intersects(st_geomfromtext('POINT('||a.dhp_epicenter_longitude||' '||a.dhp_epicenter_latitude||')',4490), t.geom) and t.name='" + name + "' and a.del_flag ='0' ", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String province, String taskYear) {
        return jdbcTemplate.queryForObject("select count(a.*) from data_his_pr a,district_boundary_province t where st_intersects(st_geomfromtext('POINT('||a.dhp_epicenter_longitude||' '||a.dhp_epicenter_latitude||')',4490), t.geom) and t.name=? and (TO_CHAR(dhp_update_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(dhp_create_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ?) and a.del_flag ='0' ", Integer.class, province, taskYear + "%", taskYear + "%");
    }
}
